<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
namespace app\api\model;

use app\common\model\BaseModel;

class DataExportTaskModel extends BaseModel
{
    function add($city_id, $name, $type, $id, $tag, $sql, $struct)
    {
        $cfg = get_vxk_config();
        $params = [
            'platform' => 'cc',
            'work_tag' => $id,
            'ex_query_sql' => $sql,
            'ex_table_structure' => json_encode($struct),
            'ex_table_name' => $name,
            'call_back_url' => $cfg['bus_server_url'] . '/task/progress',
        ];

        $url = $cfg['data_export_task_server_url'];
        $result = common::curlpostjson($url, $params, ['token: qkxy000']);
        if (empty($result) || $result['status'] !== 0) {
            hilog::error("export data task failed name= {$name}, tag={$id}");
            throw_myexception(X_ERROR, "新增任务失败，请重试!");
        }

        $data = [
            'city_id' => $city_id,
            'id' => $id,
            'tag' => $tag,
            'type' => $type,
            'progress' => 0,
            'create_time' => date('Y-m-d H:i:s'),
            'cost' => 0,
            'name' => $name,
        ];

        hisql::insert('tb_data_export_task', $data);
        return $data;
    }

    function update_progress($id, $progress, $output)
    {
        $data = [
            'progress' => $progress,
        ];
        if ($progress == 100) {
            $data['cost'] = $output['cost'];
            $data['export_file_url'] = $output['url'];
        }

        $where = [
            'id' => $id,
        ];

        hisql::update('tb_data_export_task', $data, $where);
    }

    function query_list($city_id, $type, $pos, $limit)
    {
        $selectlist = [
            'name' => 'string',
            'progress' => 'int',
            'export_file_url' => 'string',
            'cost' => 'int',
        ];

        $sfs = implode(',', array_keys($selectlist));

        $st = "select SQL_CALC_FOUND_ROWS $sfs from tb_data_export_task where city_id = ? and type = ?  order by create_time desc limit $pos, $limit";
        $beans = [$city_id, $type];

        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        $l = [];
        foreach ($rows as $row) {
            $l[] = common::sql_row($selectlist, $row);
        }
        return $l;
    }

    function query_info_by_tag($city_id, $type, $tag)
    {
        $selectlist = [
            'name' => 'string',
            'progress' => 'int',
            'export_file_url' => 'string',
            'cost' => 'int',
        ];

        $sfs = implode(',', array_keys($selectlist));

        $st = "select SQL_CALC_FOUND_ROWS $sfs from tb_data_export_task where city_id = ? and type = ? and tag = ? ";
        $beans = [$city_id, $type, $tag];

        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        foreach ($rows as $row) {
            return common::sql_row($selectlist, $row);
        }
        return null;
    }
}
const FR_TYPE_USER_ORDER = 'USER_ORDER';
const FR_TYPE_USER_ORDER_ADJUSTED = 'USER_ORDER_ADJUST';
const FR_TYPE_USER_MEMBERSHIP_ORDER = 'USER_MEMBERSHIP_ORDER';
const FR_TYPE_USER_CHARGE_ORDER = 'USER_CHARGE_ORDER';
const FR_TYPE_UNDERBALANCE_USER = 'UNDERBALANCE_USER';


class FinReportModel
{
    function user_order_list($city_map, $city_id, $start_time, $end_time, $pos, $limit)
    {
        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'bike_sn' => 'string',
            'user_id' => 'string',
            'paid_amount' => 'string',
            'user_name' => 'string',
            'end_time' => 'string',
        ];

        $st = "select " . implode(',', array_keys($selectlist)) . " from tb_user_ride_order where 1=1";
        $beans = [];
        if (!empty($city_id)) {
            hisql::bound_arg('city_id', $city_id, $st, $beans);
        }

        hisql::bound_arg('create_time', $start_time, $st, $beans, '>=');
        hisql::bound_arg('create_time', $end_time, $st, $beans, '<=');

        $st = $st . "order by create_time limit $pos, $limit";

        $l = [];
        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        foreach ($rows as $row) {
            $e = common::sql_row($selectlist, $row);
            $e['city_name'] = $city_map[$e['city_id']];
            unset($e['city_id']);
            $l[] = $e;
        }

        return [$l, hisql::query_count()];
    }

    function user_order_export($city_name, $city_id, $start_time, $end_time)
    {
        $task_type = FR_TYPE_USER_ORDER;
        $st = date('YmdHis', strtotime($start_time));
        $et = date('YmdHis', strtotime($end_time));
        $task_tag = "{$task_type}_{$city_id}_{$st}_{$et}";
        $task_id = $task_tag . "_" . time();
        $task_info = (new DataExportTaskModel())->query_info_by_tag($city_id, $task_type, $task_tag);
        if ($task_info && $task_info['progress'] >= 0) {
            return $task_info;
        }

        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'bike_sn' => 'string',
            'user_id' => 'string',
            'paid_amount' => 'string',
            'user_name' => 'string',
            'create_time' => 'string',
        ];

        $sql = "select " . implode(',', array_keys($selectlist)) . " from tb_user_ride_order where city_id = "
            . hisql::db()->escapeString($city_id) . " and create_time >=" . hisql::db()->escapeString($start_time)
            . " and create_time <=" . hisql::db()->escapeString($end_time)
            . " order by create_time";


        $struct = [
            [":default({$city_name})", '城市'],
            ['id', '流水号'],
            ['bike_sn', '车辆编号'],
            [':price(paid_amount)', '订单金额'],
            ['user_name', '用户'],
            ['create_time', '骑行时间'],
        ];


        $name = "{$city_name}_骑行订单_" . date('Ymd', strtotime($st)) . "_" . date('Ymd', strtotime($et));

        $data = (new DataExportTaskModel())->add($city_id,
            $name,
            $task_type,
            $task_id,
            $task_tag,
            $sql,
            $struct);

        return $data;
    }

    function user_order_adjust_amount_list($city_map, $city_id, $start_time, $end_time, $pos, $limit)
    {
        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'bike_sn' => 'string',
            'user_id' => 'string',
            'original_amount' => 'int',
            'adjusted_amount' => 'int',
            'user_name' => 'string',
            'create_time' => 'string',
        ];

        $st = "select SQL_CALC_FOUND_ROWS " . implode(',', array_keys($selectlist)) . " from tb_user_ride_order_adjust_amount where 1=1";
        $beans = [];
        if (!empty($city_id)) {
            hisql::bound_arg('city_id', $city_id, $st, $beans);
        }

        hisql::bound_arg('create_time', $start_time, $st, $beans, '>=');
        hisql::bound_arg('create_time', $end_time, $st, $beans, '<=');

        hisql::bound_arg('order_state', USER_CHARGE_ORDER_STATE_FINISHED, $st, $beans, '>=');
        $st = $st . "order by create_time limit $pos, $limit";

        $l = [];
        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        foreach ($rows as $row) {
            $e = common::sql_row($selectlist, $row);
            $e['city_name'] = $city_map[$e['city_id']];
            unset($e['city_id']);
            $l[] = $e;
        }

        return [$l, hisql::query_count()];
    }

    function user_order_adjust_amount_list_export($city_name, $city_id, $start_time, $end_time)
    {
        $task_type = FR_TYPE_USER_ORDER_ADJUSTED;
        $st = date('YmdHis', strtotime($start_time));
        $et = date('YmdHis', strtotime($end_time));
        $task_tag = "{$task_type}_{$city_id}_{$st}_{$et}";
        $task_id = $task_tag . "_" . time();
        $task_info = (new DataExportTaskModel())->query_info_by_tag($city_id, $task_type, $task_tag);
        if ($task_info && $task_info['progress'] >= 0) {
            return $task_info;
        }

        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'bike_sn' => 'string',
            'user_id' => 'string',
            'original_amount' => 'int',
            'adjusted_amount' => 'int',
            'user_name' => 'string',
            'create_time' => 'string',
        ];

        $sql = "select " . implode(',', array_keys($selectlist)) . " from tb_user_ride_order_adjust_amount where city_id = "
            . hisql::db()->escapeString($city_id) . " and create_time >=" . hisql::db()->escapeString($start_time)
            . " and create_time <=" . hisql::db()->escapeString($end_time)
            . " order by create_time";
        $struct = [
            [":default({$city_name})", '城市'],
            ['id', '流水号'],
            ['bike_sn', '车辆编号'],
            [':price(original_amount)', '订单金额'],
            [':price(adjusted_amount)', '调账金额'],
            ['user_name', '用户'],
            ['create_time', '调账时间'],
        ];

        $data = (new DataExportTaskModel())->add($city_id,
            "{$city_name}_用户订单调账_{$st}_{$et}",
            $task_type,
            $task_id,
            $task_tag,
            $sql,
            $struct);

        return $data;
    }

    function user_membership_order_list($city_map, $city_id, $start_time, $end_time, $pos, $limit)
    {
        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'amount' => 'string',
            'user_id' => 'string',
            'user_name' => 'string',
            'create_time' => 'string',
            'end_time' => 'string',
        ];

        $st = "select SQL_CALC_FOUND_ROWS " . implode(',', array_keys($selectlist)) . " from tb_user_membership_order where 1=1";
        $beans = [];
        if (!empty($city_id)) {
            hisql::bound_arg('city_id', $city_id, $st, $beans);
        }

        hisql::bound_arg('create_time', $start_time, $st, $beans, '>=');
        hisql::bound_arg('create_time', $end_time, $st, $beans, '<=');

        hisql::bound_arg('order_state', PAY_ORDER_STATE_FINISH, $st, $beans);
        //hisql::bound_arg('order_state', PAY_ORDER_STATE_REFUND, $st, $beans, '<=');

        $st = $st . "order by create_time limit $pos, $limit";

        $l = [];
        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        foreach ($rows as $row) {
            $e = common::sql_row($selectlist, $row);
            if (empty($e['city_id'])) {
                $e['city_name'] = '点点';
            } else {
                $e['city_name'] = $city_map[$e['city_id']];
            }
            unset($e['city_id']);
            $l[] = $e;
        }

        return [$l, hisql::query_count()];
    }

    function user_membership_order_list_export($city_name, $city_id, $start_time, $end_time)
    {
        $task_type = FR_TYPE_USER_MEMBERSHIP_ORDER;
        $st = date('YmdHis', strtotime($start_time));
        $et = date('YmdHis', strtotime($end_time));
        $task_tag = "{$task_type}_{$city_id}_{$st}_{$et}";
        $task_id = $task_tag . "_" . time();
        $task_info = (new DataExportTaskModel())->query_info_by_tag($city_id, $task_type, $task_tag);
        if ($task_info && $task_info['progress'] >= 0) {
            return $task_info;
        }

        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'amount' => 'string',
            'user_id' => 'string',
            'user_name' => 'string',
            'create_time' => 'string',
        ];

        $sql = "select " . implode(',', array_keys($selectlist)) . " from tb_user_membership_order where city_id = "
            . hisql::db()->escapeString($city_id) . " and create_time >=" . hisql::db()->escapeString($start_time)
            . " and create_time <=" . hisql::db()->escapeString($end_time)
            . " and order_state = " . USER_MEMBERSHIP_ORDER_STATE_FINISHED
            . " order by create_time";

        $struct = [
            [":default({$city_name})", '城市'],
            ['id', '流水号'],
            [':price(amount)', '订单金额'],
            ['user_name', '用户'],
            ['create_time', '时间'],
        ];


        $name = "{$city_name}免押金卡_" . date('Ymd', strtotime($st)) . "_" . date('Ymd', strtotime($et));

        $data = (new DataExportTaskModel())->add($city_id,
            $name,
            $task_type,
            $task_id,
            $task_tag,
            $sql,
            $struct);

        return $data;
    }

    function user_charge_order_list($city_map, $city_id, $start_time, $end_time, $pos, $limit)
    {
        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'amount' => 'string',
            'user_id' => 'string',
            'user_name' => 'string',
            'create_time' => 'string',
            'end_time' => 'string',
        ];

        $st = "select SQL_CALC_FOUND_ROWS " . implode(',', array_keys($selectlist)) . " from tb_user_charge_order where 1=1";
        $beans = [];
        if (!empty($city_id)) {
            hisql::bound_arg('city_id', $city_id, $st, $beans);
        }

        hisql::bound_arg('create_time', $start_time, $st, $beans, '>=');
        hisql::bound_arg('create_time', $end_time, $st, $beans, '<=');
        hisql::bound_arg('order_state', USER_CHARGE_ORDER_STATE_FINISHED, $st, $beans);

        $st = $st . "order by create_time limit $pos, $limit";

        $l = [];
        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        foreach ($rows as $row) {
            $e = common::sql_row($selectlist, $row);
            if (empty($e['city_id'])) {
                $e['city_name'] = '点点';
            } else {
                $e['city_name'] = $city_map[$e['city_id']];
            }
            unset($e['city_id']);
            $l[] = $e;
        }

        return [$l, hisql::query_count()];
    }

    function user_charge_order_list_export($city_name, $city_id, $start_time, $end_time)
    {
        $task_type = FR_TYPE_USER_CHARGE_ORDER;
        $st = date('YmdHis', strtotime($start_time));
        $et = date('YmdHis', strtotime($end_time));
        $task_tag = "{$task_type}_{$city_id}_{$st}_{$et}";
        $task_id = $task_tag . "_" . time();
        $task_info = (new DataExportTaskModel())->query_info_by_tag($city_id, $task_type, $task_tag);
        if ($task_info && $task_info['progress'] >= 0) {
            return $task_info;
        }

        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'amount' => 'string',
            'user_id' => 'string',
            'user_name' => 'string',
            'create_time' => 'string',
        ];

        $sql = "select " . implode(',', array_keys($selectlist)) . " from tb_user_charge_order where city_id = "
            . hisql::db()->escapeString($city_id) . " and create_time >=" . hisql::db()->escapeString($start_time)
            . " and create_time <=" . hisql::db()->escapeString($end_time)
            . " and order_state=" . USER_CHARGE_ORDER_STATE_FINISHED
            . " order by create_time";

        $struct = [
            [":default({$city_name})", '城市'],
            ['id', '流水号'],
            [':price(amount)', '金额'],
            ['user_name', '用户'],
            ['create_time', '时间'],
        ];


        $name = "{$city_name}_用户充值_" . date('Ymd', strtotime($st)) . "_" . date('Ymd', strtotime($et));
        $data = (new DataExportTaskModel())->add($city_id,
            $name,
            $task_type,
            $task_id,
            $task_tag,
            $sql,
            $struct);

        return $data;
    }

    function poor_user_list($city_map, $city_id, $pos, $limit)
    {
        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'name' => 'string',
            'phone' => 'string',
            'balance' => 'int',
            'create_time' => 'string',
        ];

        $st = "select SQL_CALC_FOUND_ROWS " . implode(',', array_keys($selectlist)) . " from tb_user  where balance<0";
        $beans = [];
        if (!empty($city_id)) {
            hisql::bound_arg('city_id', $city_id, $st, $beans);
        }
        $st = $st . " order by create_time limit $pos, $limit";

        $l = [];
        $result = hisql::db()->query($st, $beans);
        $rows = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        foreach ($rows as $row) {
            $e = common::sql_row($selectlist, $row);
            $e['city_name'] = $city_map[$e['city_id']];
            unset($e['city_id']);
            $l[] = $e;
        }

        return [$l, hisql::query_count()];
    }

    function underbalance_user_export($city_name, $city_id)
    {
        $task_type = FR_TYPE_UNDERBALANCE_USER;
        $nowdate = date('YmdHis');
        $task_tag = "{$task_type}_{$city_id}_$nowdate";
        $task_id = $task_tag . "_" . time();
        $task_info = (new DataExportTaskModel())->query_info_by_tag($city_id, $task_type, $task_tag);
        if ($task_info && $task_info['progress'] >= 0) {
            return $task_info;
        }

        $selectlist = [
            'city_id' => 'string',
            'id' => 'string',
            'name' => 'string',
            'phone' => 'string',
            'balance' => 'int',
            'create_time' => 'string',
        ];

        $sql = "select " . implode(',', array_keys($selectlist)) . " from tb_user where city_id = "
            . hisql::db()->escapeString($city_id) . " and balance < 0";

        $struct = [
            [":default({$city_name})", '城市'],
            ['id', '用户ID'],
            ['name', '用户名'],
            [':price(balance)', '余额'],
            ['create_time', '注册时间'],
        ];

        $data = (new DataExportTaskModel())->add($city_id,
            "{$city_name}_欠费用户_{$nowdate}",
            $task_type,
            $task_id,
            $task_tag,
            $sql,
            $struct);

        return $data;
    }
}
